package yiguan;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Hashtable;
import org.json.JSONObject;
import org.json.JSONArray;
import org.json.JSONException;
import org.apache.log4j.Logger;

public class Waiter {
	private static Logger logger = Logger.getLogger("Waiter");

	private String wname_;
	private String mobile_;
	private String imei_;
	private String idcard_;
	private String comid_;
	private String comname_;
	private String qualify_;
	private String district_;
	private int state_;


	public Waiter(final String wname, final String mobile, final String imei) {
		wname_ = wname;
		mobile_ = mobile;
		imei_ = imei;
	}

	public Waiter(final String imei) {
		this("", "", imei);
	}

	public boolean register() {
		boolean retval = false;
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = DBUtil.openConnection();
			ps = conn.prepareStatement("insert into waiter(imei, wname, mobile) values(?,?,?)");
			ps.setString(1, imei_);
			ps.setString(2, wname_);
			ps.setString(3, mobile_);
			int rownum = ps.executeUpdate();
			if (rownum == 1)
				retval = true;
			logger.info(wname_ + " - " + mobile_ + " - registered!");
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	public boolean update(String wname, String mobile, String idcard, String comid, int state) {
		boolean retval = false;
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = DBUtil.openConnection();
			ps = conn.prepareStatement("update waiter set wname=?,mobile=?,idcard=?,comid=?,state=? where imei=?");
			ps.setString(1, wname);
			ps.setString(2, mobile);
			ps.setString(3, idcard);
			ps.setString(4, comid);
			ps.setInt(5, state);
			ps.setString(6, imei_);
			int rownum = ps.executeUpdate();
			if (rownum == 1)
				retval = true;
			logger.info(wname + " - " + mobile + " updated : state= " + state);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	public static boolean deleteWaiterByMobile(String imei) {
		boolean retval = false;
		Connection conn = null;
		PreparedStatement ps = null;

		Waiter w = getWaiterByImei(imei);
		if (w == null) {
			logger.error(imei + " not found!");
			return false;
		}

		try {
			conn = DBUtil.openConnection();
			ps = conn.prepareStatement("delete from waiter where imei=?");
			ps.setString(1, imei);
			int rownum = ps.executeUpdate();
			if (rownum == 1)
				retval = true;
			logger.info(w.getWname() + " - " + w.getMobile() + " deleted!");
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	public JSONObject toJSON() {
		JSONObject so = new JSONObject();
		try{
			so.put("id", imei_);
			String[] ss = new String[9];
			ss[0] = imei_;
			ss[1] = wname_;
			ss[2] = mobile_;
			ss[3] = idcard_;
			ss[4] = comid_;
			ss[5] = comname_;
			ss[6] = String.valueOf(state_);
			ss[7] = qualify_;
			ss[8] = district_;
			so.put("cell", ss);
		} catch(JSONException e) {}

		return so;
	}

	public static Waiter[] getAllWaiter(Hashtable params) {
		ArrayList<Waiter> list = new ArrayList<Waiter>();

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sqlWhere = "";

		if ((params != null) && !params.isEmpty())
			sqlWhere = DBUtil.buildSqlWhere(params);

		try {
			conn = DBUtil.openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select imei,mobile,wname,idcard,comid,comname,state,qualify,district from waiter left join laborcom on waiter.comid=laborcom.id" + sqlWhere);
			while(rs.next()) {
				Waiter w = new Waiter(rs.getString("wname").trim(), rs.getString("mobile").trim(), rs.getString("imei").trim());
				w.idcard_ = rs.getString("idcard") == null? "":rs.getString("idcard").trim();
				w.comid_ = rs.getString("comid") == null? "":rs.getString("comid");
				w.comname_ = rs.getString("comname") == null? "":rs.getString("comname").trim();
				w.state_ = rs.getInt("state");
				w.qualify_ = rs.getString("qualify") == null? "":rs.getString("qualify").trim();
				w.district_ = rs.getString("district") == null? "":rs.getString("district");
				list.add(w);
			}
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		Waiter[] ws = (Waiter[])list.toArray(new Waiter[0]);
		return ws;
	}

	public static Waiter getWaiterByImei(String imei) {
		String sqlWhere = " where imei=?";

		return getWaiter(sqlWhere, imei);
	}

	public static Waiter getWaiterByMobile(String mobile) {
		String sqlWhere = " where mobile=?";

		return getWaiter(sqlWhere, mobile);
	}

	private static Waiter getWaiter(String sqlWhere, String val) {
		Waiter waiter = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.openConnection();
			ps = conn.prepareStatement("select * from waiter" + sqlWhere);
			ps.setString(1, val);
			rs = ps.executeQuery();
			if (rs.next()) {
				String imei  = rs.getString("imei");
				String wname = rs.getString("wname").trim();
				String mobile = rs.getString("mobile").trim();
				waiter = new Waiter(wname, mobile, imei);
				waiter.idcard_ = rs.getString("idcard") == null? "" : rs.getString("idcard").trim();
				waiter.comid_ = rs.getString("comid") == null? "" : rs.getString("comid");
				waiter.state_ = rs.getInt("state");
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return waiter;
	}

	public static JSONArray getAllWaiterByCom() {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		JSONArray sa = new JSONArray();

		try {
			conn = DBUtil.openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select comid,wname,mobile from waiter where state=1 order by comid");
			while (rs.next()) {
				JSONObject w = new JSONObject();
				w.put("comid", rs.getString("comid"));
				w.put("wname", rs.getString("wname").trim());
				w.put("mobile", rs.getString("mobile").trim());
				sa.put(w);
			}
		} catch(Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return sa;
	}

	//getter and setter
	public String getMobile() {
		return mobile_;
	}

	public String getWname() {
		return wname_;
	}

	public int getState() {
		return state_;
	}

	public String getIdCard() {
		return idcard_;
	}

	public String getComid() {
		return comid_;
	}
}